 require 'rubygems' 
 require 'spreadsheet'
=begin
  Convert a RTable::Table to an excel file ".xls"
  Convert an Excel file to a RTable::Table
  * Handles font foreground, underline, strikeout(through),italic
    in both to and from .xls 
=end
module Table
 #  method to call from a Table::RTable
 #  returns Spreadsheet::Worksheet of RTable
 def do_excel(name,table)
  book = create_book
  make_sheet book,name,table
 end

 # Creates and returns blank Spreadsheet::Workbook 
 def create_book
  Spreadsheet.client_encoding = 'UTF-8'
  book = Spreadsheet::Workbook.new
  book
 end

 # adds a Spreadsheet::Worksheet to a Spreadsheet::Workbook of a RTable
 # returns [book,sheet] (Spreadsheet::)
 def make_sheet book,name,table
  sheet1 = book.create_worksheet
  sheet1.name = name

  table.rows.each_with_index {|r,row|
    r[:cols].each_with_index {|c,col|
      sheet1[row,col] = c
    }
  }
  fmatted = []
  table.formats.each_key {|f|
    format = table.formats[f][2]
    unless fmatted.index(f)
      sheet1.row(table.formats[f][0].to_i).set_format(table.formats[f][1].to_i,format)
    end
    x,y  = table.formats[f][0].to_i,table.formats[f][1].to_i
    if table.also.has_key?("#{x}:#{y}")
     table.also["#{x}:#{y}"].each {|col|
      format = table.formats["#{x}:#{col}"][2]
      fmatted << "#{x}:#{col}"
# p fmatted
      sheet1.row(x).set_format(col,format)
     }
    end
  }
  [book,sheet1]
 end

 # Create a Spreadsheet::Workbook and Table::RBook from a excel file
 # Fill Worksheet 's and RTable 's to respective book from file
 # file => the excel file path => string
 # returns [aRBook,aWorkbook]
 def from_excel(file)
  Spreadsheet.client_encoding = 'UTF-8'
  book = Spreadsheet.open file
  File.basename(file) =~ /^(.*)\.xls/
  name = $1
  rbook=RBook::new
  rbook.bname = name
  rbook.fpath = File.dirname(file)
  book.worksheets.each {|sheet|
    t = RTable.new
    len = []
    sheet.each_with_index {|row,i|
        if i > 0
          trow = t.add_row
        end
      row.each_with_index {|col,ind|
        len[ind]=0
        str = sheet.cell(i,ind).to_s
        hash = {
         :ul => row.format(ind).font.instance_variable_get("@underline"),
         :so => row.format(ind).font.instance_variable_get("@strikeout"),
         :weight => row.format(ind).font.instance_variable_get("@weight"),
         :fg => row.format(ind).font.instance_variable_get("@color")
        }
        opts = []
        if hash[:ul] != :none
          opts << "underline"
        end 
        if hash[:so] != false
          opts << "strike"
        end 
        if hash[:weight] > 400
          opts << "bold"
        end
        if hash[:fg] != :text
          opts << "fore:"+hash[:fg].to_s
        end
        t.color_xy(i,ind,*opts)
        len[ind] = str.length if str.length > len[ind]
        t.fill_xy(str,i,ind)
      }
    }
    rbook << t
  }
  [rbook,book]
 end
end
__END__


